package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.DTRequestDto;
import com.hualude.roadsign.dto.HistoryDto;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * Created by xu-jiaHui on 2017/6/8.
 */
public class HistoryRepositoryImpl {
    @PersistenceContext
    private EntityManager em;

    public List<HistoryDto> getHistory4DT(DTRequestDto dtRequestDto,
                                          Integer equipmentId,
                                          Integer equipmentType) throws Exception {
        String sql = "select h.*, u.UserName as operatingPerson " +
                "from tb_history h, tb_user u " +
                "where u.UserId = h.operatorId " +
                "and h.equipmentId = " + equipmentId + " " +
                "and h.equipmentType = " + equipmentType + " " +
                "and h.operatingTime like '%" + dtRequestDto.getSearch() + "%' " +
                "order by h.operatingTime desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(HistoryDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getHistoryCount(Integer equipmentId,
                                   Integer equipmentType,
                                   String search) throws Exception {
        String sql = "select count(*) " +
                "from tb_history h, tb_user u " +
                "where u.UserId = h.operatorId " +
                "and h.equipmentId = " + equipmentId + " " +
                "and h.equipmentType = " + equipmentType + " " +
                "and h.operatingTime like '%" + search + "%'";
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<HistoryDto> getHistory4APP(Integer equipmentId,
                                           Integer equipmentType,
                                           Integer mCount,
                                           Integer mSize) throws Exception {
        String sql = "select h.*, u.UserName as OperatingPerson " +
                "from tb_history h, tb_user u " +
                "where u.UserId = h.operatorId " +
                "and h.equipmentId = " + equipmentId + " " +
                "and h.equipmentType = " + equipmentType + " " +
                "order by h.operatingTime desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(HistoryDto.class));
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

}
